﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ebiz.libs;
using System.Data.SqlClient;
using System.Data;
using System.Net.Mail;
using System.Net;

namespace Ebiz
{
    public partial class PostBulk : System.Web.UI.Page
    {
        public string errMsg = "";
        public enum EmailMode
        {
            REQUEST, APPROVED, RETURNED, REJECTED
        }
        protected void Page_Load(object sender, EventArgs e){
            if (!IsPostBack){
                if (Request.QueryString["postbulk"] != null &&
                        !(string.IsNullOrEmpty(Request.QueryString["postbulk"]))){

                    string CLNum = Request.QueryString["postbulk"].ToString();
                    if (isBulkUploading()){
                        if ((isDataExist("CLNum", CLNum, "tbl_BulkMaster") == true) && 
                            (isDataExist("CLNum", CLNum, "tbl_Request") == true)) {

                                if (isDataExist("CLNum", CLNum, "tbl_RequestCustomer") == false) {
                                    //CheckTable(CLNum);
                                    SaveReqCustomer(CLNum);
                                }else{
                                    Response.Write("Error (E-P01): Invalid Operation Detected!");
                                    Response.End();
                                }
                        }else{
                            Response.Write("Error (E-P02): Invalid Operation Detected!");
                            Response.End();
                        }
                    }else{
                        Response.Write("Error (E-P03): Invalid Operation Detected!");
                        Response.End();
                    }
                }else{
                    Response.Write("Error (E-P04): Invalid Operation Detected!");
                    Response.End();
                }
            }
        }
        private void CheckTable(string CLNumber)
        {
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn)
            {
                
                try
                {
                    cn.Open();
                    
                    #region check view is exist
                    string sql = "select * from " + DBName + ".dbo.v_mapBulkCopy ";
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    SaveReqCustomer(CLNumber);

                }
                catch (Exception ex)
                {
                    try
                    {
                        #region create view
                       string strQuery ="CREATE VIEW [dbo].[v_mapBulkCopy] " +
                           " AS " +
                           " SELECT  bm.DistributorCode, bm.Channel, bm.IDControl, bm.Value1, bm.ApprovalCurrent, bm.ApprovalMax, " +
                           " bm.CreatedBy, bm.StatusCode, bd.IDSeq,bd.CLNum, bd.ProductCode, bd.ProdDivision, bd.ProductName,  " +
                           " bd.Discount, bd.Quantity, bd.CustGroup, bd.CustCode, bd.CSCodeAZI, bd.CustName, bd.CustAddress " +
                           " FROM dbo.tbl_BulkMaster AS bm INNER JOIN dbo.tbl_BulkDetail AS bd ON bd.CLNum = bm.CLNum ";
                        #endregion
                        SqlCommand cmd = new SqlCommand(strQuery, cn);
                        cmd.ExecuteNonQuery();
                        SaveReqCustomer(CLNumber);

                    }
                    catch(Exception exs)
                    {
                        RollBackUpload(CLNumber);
                    }

                }
                finally
                {
                    cn.Close();
                }
            }
            #endregion
        }
        private void SaveReqCustomer(string CLNumber){
            string DBName = DBUtilities.GetDBName("devCon");
            string sql = "INSERT INTO " + DBName + ".dbo.tbl_RequestCustomer " +
                "(CLNum, DistributorCode, CustGroup, " +
                "CustCode, CSCodeAZI, CustName, CustAddress, StatusCode) " +
                "SELECT SourceData.CLNum, SourceData.DistributorCode, SourceData.CustGroup, " +
                "SourceData.CSCodeAZI, SourceData.CustCode, SourceData.CustName, " +
                "SourceData.CustAddress, '101' " +
                "FROM " + DBName + ".dbo.v_mapBulkCopy AS SourceData";
            if (ExecQuery(sql) > 0){
                //Proceed with save Product
                if (isDataExist("CLNum", CLNumber, "tbl_RequestProduct") == false){
                    SaveReqProduct(CLNumber);
                }else{
                    Response.Write("Error (E-P05): Invalid Operation Detected!");
                    Response.End();
                }
            }else{
                //TODO else Rollback here
                string qryEmptyMaster = "TRUNCATE TABLE " + DBName + ".dbo.tbl_BulkMaster";
                string qryEmptyDetail = "TRUNCATE TABLE " + DBName + ".dbo.tbl_BulkDetail";
                int ExecMaster = ExecQuery(qryEmptyMaster);
                int ExecDetail = ExecQuery(qryEmptyDetail);

                ////Set Status Operation to 0 ------------
                SetBulkStatus(0);
                Response.Write("Error: Bulk Upload Failed!");
                Response.End();
            }
        }

        private void SaveReqProduct(string CLNumber){
            try
            {
                string DBName = DBUtilities.GetDBName("devCon");
                string sql = "INSERT INTO " + DBName + ".dbo.tbl_RequestProduct (CLNum, " +
                    "DistributorCode, ProductCode, ProdDivision, ProductName, Discount, " +
                    "Quantity, StatusCode) " +
                    "SELECT SourceData.CLNum, SourceData.DistributorCode, SourceData.ProductCode, " +
                    "SourceData.ProdDivision, SourceData.ProductName, SourceData.Discount, " +
                    "SourceData.Quantity, '101' " +
                    "FROM " + DBName + ".dbo.v_mapBulkCopy AS SourceData";

                int ExecStatus = ExecQuery(sql);
                if (ExecStatus > 0)
                {
                    string sMessage = "<script>alert('Bulk Upload Success!\\n(" + ExecStatus +
                        " Rows Data Inserted)');" +
                        "window.location='Home.aspx';</script>";
                    string ShowMsg = "";

                    //Proceed with Sending Email --------------------------
                    Dictionary<string, object> dctSYS = Func.GetSysConfig();
                    string _Port = dctSYS["ServerPort"].ToString();
                    string SYSPort = "/";
                    string SYSUrl = dctSYS["ServerAddress"].ToString() + SYSPort;
                    string Token = Func.GetUserInfo(Func.ShowInfo._SSID);
                    string PageQry = "id=" + CLNumber + "&atdn=" + Token;
                    string PageLink = SYSUrl + "Default.aspx?cl=" + CryptLib.Base64(PageQry, CryptMode.ENCRYPT);

                    Dictionary<string, string> apvTarget = GetApvData(CLNumber);
                    Dictionary<string, object> SendAppEmail = SendEmail(apvTarget["Email"], CLNumber, PageLink, apvTarget["FName"], EmailMode.REQUEST);
                    int EmailSent = (int)SendAppEmail["Status"];
                    if (EmailSent == 1)
                    {
                        //Update tbl_RequestApproval with EmailSent = 1 --------------
                        string qryEmail = "UPDATE " + DBName + ".dbo.tbl_RequestApproval " +
                            "SET EmailDate = getdate(), EmailSent = 1 WHERE (CLNum = @CLNum)";
                        Dictionary<string, object> dctParamsEmail = new Dictionary<string, object>();
                        dctParamsEmail.Add("CLNum", CLNumber);
                        if (ExecQuery(qryEmail, dctParamsEmail) > 0)
                        {
                            //Empty Bulk Tables --------------------
                            string qryEmptyMaster = "TRUNCATE TABLE " + DBName + ".dbo.tbl_BulkMaster";
                            string qryEmptyDetail = "TRUNCATE TABLE " + DBName + ".dbo.tbl_BulkDetail";
                            int ExecMaster = ExecQuery(qryEmptyMaster);
                            int ExecDetail = ExecQuery(qryEmptyDetail);

                            //Set Status Operation to 0 ------------
                            SetBulkStatus(0);
                            ShowMsg = sMessage;
                        }
                    }
                    else
                    {
                        ShowMsg = "<script>alert('" + SendAppEmail["Message"].ToString() + "');" +
                            "window.location='Home.aspx';</script>";
                    }

                    Response.Write(ShowMsg);
                }
                else
                {
                    //TODO else Rollback here ------------------
                    RollBackUpload(CLNumber);
                    Response.Write("Error: Bulk Upload Failed!");
                    Response.End();
                }
            }
            catch
            {
                RollBackUpload(CLNumber);
                Response.Write("Error :Bulk Upload Fiiled!");
                Response.End();
            }
        }

        private static Dictionary<string, string> GetApvData(string CLNum) {
            Dictionary<string, string> result = new Dictionary<string, string>();
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();

                    string qry = "SELECT tbl_Request.ApprovalCurrent, " +
                        "tbl2.FullName, tbl2.Email " +
                        "FROM " + DBName + ".dbo.tbl_Request " +
                        "INNER JOIN " + DBName + ".dbo.UserAccounts AS tbl2 " +
                        "ON tbl2.UserName = tbl_Request.ApprovalCurrent  " +
                        "WHERE (tbl_Request.CLNum = '" + CLNum + "') " +
                        "AND (tbl_Request.ApprovalStep = 1) " +
                        "AND (tbl_Request.StatusCode = '210')";

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            string uID = rdr.GetValue(0).ToString();
                            string uFName = rdr.GetValue(1).ToString();
                            string uEmail = rdr.GetValue(2).ToString();

                            result.Add("ID", uID);
                            result.Add("FName", uFName);
                            result.Add("Email", uEmail);
                        }
                    }
                }catch(Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static Dictionary<string, object> SendEmail(string MailTo, string CLNumber, 
            string PageLink, string TFullName, EmailMode SendMode){

            Dictionary<string, object> result = new Dictionary<string, object>();

            //string EmailAddress = GetEmail(MailTo);
            string EmailAddress = MailTo;
            string EmailSubject = "BizCase request with CL Number " + CLNumber;
            if (!(string.IsNullOrEmpty(EmailAddress))){
                Dictionary<string, object> dctConfig = GetEmailConfig();

                MailMessage Mailer = new MailMessage();
                Mailer.Subject = EmailSubject;
                Mailer.From = new MailAddress(dctConfig["FromEmail"].ToString(), dctConfig["FromName"].ToString());
                Mailer.Priority = (MailPriority)Convert.ToInt32(dctConfig["Priority"]);
                Mailer.IsBodyHtml = (bool)Convert.ToBoolean(dctConfig["IsBodyHTML"]);

                Mailer.To.Add(EmailAddress);
                Mailer.CC.Add(Func.GetUserInfo(Func.ShowInfo._Email));

                Dictionary<string, string> dctEmail = new Dictionary<string, string>();
                dctEmail["Subject"] = EmailSubject;
                dctEmail["Requester"] = Func.GetUserInfo(Func.ShowInfo._FullName);
                //dctEmail["AppvPerson"] = GetFullName(MailTo);
                dctEmail["AppvPerson"] = TFullName;
                dctEmail["AppvPersonExt"] = "";
                dctEmail["CLNum"] = CLNumber;
                dctEmail["Link"] = PageLink;

                Mailer.Body = CLSMail.GenerateMessage(dctEmail, CLSMail.MessageMode.REQUEST);

                SmtpClient _SMTP = new SmtpClient();
                _SMTP.DeliveryMethod = (SmtpDeliveryMethod)Convert.ToInt32(dctConfig["DeliveryMethod"]);
                _SMTP.UseDefaultCredentials = (bool)Convert.ToBoolean(dctConfig["DefaultCredentials"]);
                if (!(dctConfig["UserName"] == null && dctConfig["Password"] == null)){
                    _SMTP.Credentials = new NetworkCredential(dctConfig["UserName"].ToString(), dctConfig["Password"].ToString());
                }
                _SMTP.Host = (string)dctConfig["Host"].ToString();
                _SMTP.Port = (int)Convert.ToInt32(dctConfig["Port"]);
                _SMTP.EnableSsl = (bool)Convert.ToBoolean(dctConfig["EnableSSL"]);
                _SMTP.Timeout = (int)Convert.ToInt32(dctConfig["Timeout"]);

                try{
                    _SMTP.Send(Mailer);
                    result["Status"] = 1;
                    result["Message"] = "Email Sent!";
                }catch (Exception ex){
                    string ErrMsg = ex.Message;
                    result["Status"] = 0;
                    result["Message"] = ErrMsg;
                }
            }else{
                result["Status"] = 0;
                result["Message"] = "Email address is empty!";
            }
            return result;
        }

        private static Dictionary<string, object> GetEmailConfig(){
            Dictionary<string, object> result = new Dictionary<string, object>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                string DBName = DBUtilities.GetDBName("devCon");
                string qry = "SELECT ConfigName, ConfigValue, Description FROM " + 
                    DBName + ".dbo.mstConfig WHERE (ConfigGroup = 'Email')";
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        while (rdr.Read()){
                            string CFGName = rdr.GetValue(0).ToString();
                            string CFGValue = rdr.GetValue(1).ToString();
                            string CFGDesc = rdr.GetValue(2).ToString();

                            result.Add(CFGName, CFGValue);
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetEmail(string PersonID){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT Email FROM " + dbName + 
                ".dbo.UserAccounts WHERE (UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", PersonID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetFullName(string PersonID){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT FullName FROM " + dbName + 
                ".dbo.UserAccounts WHERE (UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", PersonID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ExecQuery(string query){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    result = cmd.ExecuteNonQuery();
                }
                catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ExecQuery(string query, Dictionary<string, object> Params){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    foreach (KeyValuePair<string, object> kvp in Params){
                        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                    }
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        protected static bool isBulkUploading(){
            bool result = true;

            string strCon = DBUtilities.strConnection("DevCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                sqlCon.Open();

                string strQuery = "SELECT BulkProgress FROM dbo.tbl_BulkProcess WHERE IDSeq = 1";
                SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    if (sqlReader.Read()){
                        int tmp = Convert.ToInt32(sqlReader.GetValue(0).ToString());
                        if (tmp == 0){
                            result = false;
                        }
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        protected void SetBulkStatus(int BulkStatus){
            string DBName = DBUtilities.GetDBName("devCon");
            string strCon = DBUtilities.strConnection("devCon");
            SqlConnection cn = new SqlConnection(strCon);
            string sql = "UPDATE " + DBName + ".dbo.tbl_BulkProcess " +
                "SET BulkProgress = " + BulkStatus + " WHERE " +
                "IDSeq = 1";
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.ExecuteNonQuery();
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }
        private void RollBackUpload(string CLNum)
        {
            string head = "DELETE FROM [dbo].[tbl_Request[ WHERE (CLNum = '" + CLNum + "')";
            string detail = "DELETE FROM [dbo].[tbl_RequestProduct] WHERE (CLNum = '" + CLNum + "')";
            string aprove = "DELTE FROM [dbo].[tbl_RequestApproval] WHERE (CLNum = '" + CLNum + "')";
            ExecQuery(head);
            ExecQuery(detail);
            ExecQuery(aprove);
        }
        protected static bool isDataExist(string FieldName, object FieldValue, string TableName){
            bool result = false;

            string DBName = DBUtilities.GetDBName("devCon");
            string strCon = DBUtilities.strConnection("devCon");
            SqlConnection cn = new SqlConnection(strCon);
            using (cn){
                try{
                    cn.Open();

                    string sql = "SELECT COUNT(" + FieldName + ") FROM " + DBName + ".dbo." + TableName +
                        " WHERE (" + FieldName + " = @" + FieldName + ")";
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.Parameters.AddWithValue(FieldName, FieldValue);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            int DataCount = Convert.ToInt32(rdr.GetValue(0).ToString());
                            if (DataCount > 0) {
                                result = true;
                            }
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

    }
}
